import relationalStore from '@ohos.data.relationalStore';
import {IQueryObj} from "../myTypes/index";

// 所有数据库的操作。

// 获取数据库（创建数据库）
export function getRDBStore(context){
  //数据库的配置
  const STORE_CONFIG = {
    name: 'db2308.db', // 数据库文件名
    securityLevel: relationalStore.SecurityLevel.S1 // 数据库安全级别
  };

  //获取一个RdbStore(数据库对象)
  return relationalStore.getRdbStore(context, STORE_CONFIG)
}

//创建表。
export function createTable(store){
  //定义创建表的sql语句
  const SQL_CREATE_TABLE = 'CREATE TABLE IF NOT EXISTS EMPlOYEE (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INTEGER, SALARY REAL)'; // 建表Sql语句
  //执行sql语句
  return store.executeSql(SQL_CREATE_TABLE); // 创建数据表
}

//插入一条数据

export function insertOneData(store,tablename,data){

  return store.insert(tablename, data)

}

//查询数据
export function queryData(store,tablename){

  let predicates = new relationalStore.RdbPredicates(tablename);

  // predicates.notEqualTo('NAME', 'liuyongfan');
  // predicates.orderByAsc("SALARY");

  // query：查询
  const promise =  store.query(predicates, ['ID', 'NAME', 'AGE', 'SALARY']);

  return promise.then(resultSet=>{
    // 拿到结果集的行数。
    const rowCount = resultSet.rowCount;
    let list = [];
    // 跳到结果集的第一行
    resultSet.goToFirstRow();
    for (let i = 0; i < rowCount; i++) {

      const id = resultSet.getString(resultSet.getColumnIndex("ID"));//拿到结果集当前行的name的值。
      const name = resultSet.getString(resultSet.getColumnIndex("NAME"));//拿到结果集当前行的name的值。
      const age = resultSet.getString(resultSet.getColumnIndex("AGE"))
      const salary = resultSet.getString(resultSet.getColumnIndex("SALARY"))

      const data = {
        id,
        name,
        age,
        salary
      }
      list.push(data);

      // 跳到结果集的下一行
      resultSet.goToNextRow();
    }
    resultSet.close();
    return list;
  })
    .catch(err=>{
      if (err) {
        console.error(`Failed to query data. Code:${err.code}, message:${err.message}`);
        return;
      }
    })

}


//查询数据
export function queryDataUseSql(store,tablename,queryObj:IQueryObj={}){

  // let predicates = new relationalStore.RdbPredicates(tablename);

  // query：查询
  // const sqlStr = "SELECT * FROM "+tablename+" WHERE NAME = ? AND SALARY >= ? AND SALARY <= ?";
  let sqlStr = "SELECT * FROM "+tablename+" WHERE 1=? ";


  // const promise =  store.querySql(sqlStr,[queryObj.name,queryObj.minSalary,queryObj.maxSalary]);
  const arr = [1]

  if(queryObj.name!=""){
    sqlStr += " AND NAME= ? "
    arr.push[queryObj.name];
  }
  if(queryObj.minSalary!=0){
    sqlStr += " AND SALARY >= ? "
    arr.push[queryObj.minSalary];
  }
  if(queryObj.maxSalary!=0){
    sqlStr += " AND SALARY <= ? "
    arr.push[queryObj.maxSalary];
  }

  console.log("sqlStr1",sqlStr);
  const promise =  store.querySql(sqlStr,arr);

  return promise.then(resultSet=>{
    // 拿到结果集的行数。
    const rowCount = resultSet.rowCount;
    let list = [];
    // 跳到结果集的第一行
    resultSet.goToFirstRow();
    for (let i = 0; i < rowCount; i++) {

      const id = resultSet.getString(resultSet.getColumnIndex("ID"));//拿到结果集当前行的name的值。
      const name = resultSet.getString(resultSet.getColumnIndex("NAME"));//拿到结果集当前行的name的值。
      const age = resultSet.getString(resultSet.getColumnIndex("AGE"))
      const salary = resultSet.getString(resultSet.getColumnIndex("SALARY"))

      const data = {
        id,
        name,
        age,
        salary
      }
      list.push(data);

      // 跳到结果集的下一行
      resultSet.goToNextRow();
    }
    resultSet.close();
    return list;
  })
    .catch(err=>{
      if (err) {
        console.error(`Failed to query data. Code:${err.code}, message:${err.message}`);
        return;
      }
    })

}

//删除数据
export function deleteOneData(store,tablename,id:number){
  const sqlStr = "DELETE FROM "+tablename+" WHERE ID = "+id;

  return store.executeSql(sqlStr);
}

//修改
export function updateOneData(store,tablename,id,name){
  const sqlStr = `UPDATE  ${tablename} set NAME='${name}' where id=${id}`;
  console.log("修改的sql语句",sqlStr);

  return store.executeSql(sqlStr);
}